﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using yeqw.FrameWork;
using com.gyz.hao.WinCapInfoApp.Model;

namespace com.gyz.hao.WinCapInfoApp.BLL
{
    public class MySqlDataAccess : IDisposable
    {
        private static readonly string connectionString = ConfigurationManager.AppSettings["ConnectionString"];

        private readonly MySqlConnection m_connection;
        private MySqlCommand m_command;
        private MySqlDataAdapter m_dataAdapter;
        private MySqlDataReader m_datareader;
        private MySqlTransaction m_MySqltransaction;
        private bool m_Istransaction;
        /// <summary>
        /// 实例化MySqlConnection
        /// </summary>
        public MySqlDataAccess() : this(connectionString)
        {
        }

        public MySqlDataAccess(string connString)
        {
            m_connection = new MySqlConnection(connString);
        }

        #region 处理打开，关闭数据库操作
        /// <summary>
        /// 
        /// </summary>
        public MySqlConnection ConnectionOpen()
        {
            try
            {
                if (m_connection.State == ConnectionState.Closed)
                {
                    m_connection.Open();
                }
            }
            catch (MySqlException ex)
            {
                AppConfigInfo.AppConfig.WriteMsg("打开数据库异常:" + ex.Message);
            }
            return m_connection;
        }

        public MySqlConnection ConnectionOpen(string connString)
        {
            this.m_connection.ConnectionString = connString;

            if (m_connection.State == ConnectionState.Closed)
            {
                m_connection.Open();
            }
            return m_connection;
        }


        public MySqlConnection ConnectionClose()
        {
            if (m_connection.State == ConnectionState.Open)
            {
                m_connection.Close();
            }
            return m_connection;
        }

        #endregion

        #region 事务处理
        /// <summary>
        /// 开始事务
        /// </summary>
        public void BeginTransaction()
        {
            this.ConnectionOpen();
            this.m_Istransaction = true;
            this.m_MySqltransaction = this.m_connection.BeginTransaction();
        }
        /// <summary>
        /// 开始事务
        /// </summary>
        public void BeginTransaction(string connectString)
        {
            this.ConnectionOpen(connectString);
            this.m_Istransaction = true;
            this.m_MySqltransaction = this.m_connection.BeginTransaction();
        }
        /// <summary>
        ///  提交事务
        /// </summary>
        public void Commit()
        {
            this.m_MySqltransaction.Commit();
            this.m_Istransaction = false;
            this.Dispose();
        }
        /// <summary>
        /// 回滚事务
        /// </summary>
        public void Rollback()
        {
            this.m_MySqltransaction.Rollback();
            this.m_Istransaction = false;
            this.Dispose();
        }

        #endregion


        #region 构造MySqlCommand

        private MySqlCommand MakeCommand(string MySql)
        {
            m_command = new MySqlCommand(MySql);
            return m_command;
        }

        private MySqlCommand MakeCommand(string MySql, params MySqlParameter[] parameters)
        {
            m_command = new MySqlCommand();
            m_command.CommandText = MySql;
            m_command.Parameters.Clear();
            foreach (MySqlParameter parameter in parameters)
            {
                m_command.Parameters.Add(parameter);
            }
            return m_command;
        }

        #endregion

        #region 构造 ExecuteNonQuery 插入,更新,删除数据

        public int ExecuteNonQuery(string MySql)
        {
            return ExecuteNonQuery(MakeCommand(MySql));
        }

        public int ExecuteNonQuery(string MySql, params MySqlParameter[] parameters)
        {
            return ExecuteNonQuery(MakeCommand(MySql, parameters));
        }

        public int ExecuteNonQuery(MySqlCommand command)
        {
            int resVal;
            try
            {
                if (this.m_Istransaction)
                {
                    command.Transaction = this.m_MySqltransaction;
                }
                command.Connection = ConnectionOpen();
                resVal = command.ExecuteNonQuery();
            }
            finally
            {
                if (!m_Istransaction)
                {
                    command.Dispose();
                    ConnectionClose();
                }
            }
            return resVal;

        }
        #endregion

        #region 更新DataTable同步数据
        /// <summary>
        ///  更新DataTable同步数据
        /// </summary>
        /// <param name="selectCommand"></param>
        /// <param name="dataTable"></param>
        public int UpdateDataTable(MySqlCommand selectCommand, DataTable dataTable)
        {
            MySqlCommandBuilder cmdbuilder = new MySqlCommandBuilder();
            int resVal;
            try
            {
                if (this.m_Istransaction)
                {
                    selectCommand.Transaction = this.m_MySqltransaction;
                }
                m_dataAdapter = new MySqlDataAdapter();
                selectCommand.Connection = ConnectionOpen();
                m_dataAdapter.SelectCommand = selectCommand;
                cmdbuilder.DataAdapter = m_dataAdapter;
                resVal = m_dataAdapter.Update(dataTable);
            }
            finally
            {
                m_dataAdapter.Dispose();
                selectCommand.Dispose();
                cmdbuilder.Dispose();
                if (!this.m_Istransaction)
                {
                    ConnectionClose();
                }
                // ConnectionClose();
            }
            return resVal;
        }
        #endregion

        #region 构造 FillDataSet

        public DataTable FillDataSet(string MySql)
        {
            return FillDataSet(MakeCommand(MySql));
        }

        public DataTable FillDataSet(string MySql, params MySqlParameter[] parameters)
        {
            return FillDataSet(MakeCommand(MySql, parameters));
        }
        public void FillDataSet(MySqlCommand command, DataSet ds)
        {
            try
            {

                m_dataAdapter = new MySqlDataAdapter();
                command.Connection = ConnectionOpen();
                m_dataAdapter.SelectCommand = command;
                m_dataAdapter.Fill(ds);
            }
            finally
            {
                m_dataAdapter.Dispose();
                command.Dispose();
                ConnectionClose();
            }
        }

        public DataTable FillDataSet(MySqlCommand command)
        {
            DataTable table = new DataTable();
            try
            {

                m_dataAdapter = new MySqlDataAdapter();
                command.Connection = ConnectionOpen();
                m_dataAdapter.SelectCommand = command;
                m_dataAdapter.Fill(table);
            }
            finally
            {
                m_dataAdapter.Dispose();
                command.Dispose();
                ConnectionClose();
            }
            return table;

        }

        #endregion

        #region 构造 ExecuteReader

        public MySqlDataReader ExecuteReader(string MySql)
        {
            return ExecuteReader(MakeCommand(MySql));
        }

        public MySqlDataReader ExecuteReader(string MySql, params MySqlParameter[] parameters)
        {
            return ExecuteReader(MakeCommand(MySql, parameters));
        }

        public MySqlDataReader ExecuteReader(MySqlCommand command)
        {
            try
            {

                command.Connection = ConnectionOpen();
                m_datareader = command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            finally
            {
                command.Dispose();
            }
            return m_datareader;
        }



        #endregion

        #region 构造 ExecuteScalar

        public object ExecuteScalar(string MySql)
        {
            return ExecuteScalar(MakeCommand(MySql));
        }

        public object ExecuteScalar(string MySql, params MySqlParameter[] parameters)
        {
            return ExecuteScalar(MakeCommand(MySql, parameters));
        }

        public object ExecuteScalar(MySqlCommand command)
        {
            object result;
            try
            {
                command.Connection = ConnectionOpen();
                if (this.m_Istransaction)
                {
                    command.Transaction = this.m_MySqltransaction;
                }
                result = command.ExecuteScalar();
            }
            finally
            {
                if (!this.m_Istransaction)
                {
                    command.Dispose();
                    ConnectionClose();
                }

            }
            return result;
        }
        #endregion

        #region 构造 ExecuteXmlReader


        #endregion

        public void Dispose()
        {
            ConnectionClose();

            if (m_command != null)
            {
                m_command.Dispose();
            }
            if (m_connection != null)
            {
                m_connection.Dispose();
            }
        }
    }

    public class MysqlDataBLL
    {


        /// <summary>
        /// 设置捕获状态
        /// </summary>
        /// <returns></returns>
        public ResultInfo<bool> SetCapStatus(int status,string where)
        {
            ResultInfo<bool> res = new ResultInfo<bool>();

      
            MySqlDataAccess da = new MySqlDataAccess();

            try
            {
                da.ConnectionOpen();
                string sql = string.Format("update mc_capinfo set status={0},update_time='{1}' where  {2}", status, DateTime.Now, where);
               
                int count = da.ExecuteNonQuery(sql);
                if (count > 0)
                {
                    res.IsError = false;
                    res.ResultCode = "00";
                }
                else
                {
                    res.IsError = true;
                    res.ResultCode = "更新记录没找到";
                }
            }
            catch (MySqlException ex1)
            {
                AppConfigInfo.AppConfig.WriteMsg("MYSQL出现异常!异常原因:" + ex1.Message);
                res.IsError = true;
                res.ResultCode = "出现数据库异常！";
            }
            finally
            {
                da.Dispose();
            }
            return res;

        }
        /// <summary>
        /// 获取全部集合
        /// </summary>
        /// <returns></returns>
        public List<CapInfo> GetCapInfoALLList()
        {
            return GetCapInfoList("computeno="+AppConfigInfo.AppConfig.ComputeNo);
        }

        public List<CapInfo> GetCapInfoByGroupList(int groupID)
        {
            return GetCapInfoList("computeno={0} and groupid={1}".FormatExtension(AppConfigInfo.AppConfig.ComputeNo, groupID));
        }


        private  List<CapInfo> GetCapInfoList(string where)
        {
            List<CapInfo> list = new List<CapInfo>();
            MySqlDataAccess da = new MySqlDataAccess();
            MySqlDataReader reader = null;
            try
            {
                string sql = "select * from mc_capinfo where {0} order by update_time,sort desc".FormatExtension(where);
                if(AppConfigInfo.AppConfig.GroupCollect == true)//如果是分组采集
                {
                    sql = "select * from mc_capinfo ORDER BY id LIMIT " + AppConfigInfo.AppConfig.CollectItem + "," + AppConfigInfo.AppConfig.CollectNum;
                }
                da.ConnectionOpen();
                reader = da.ExecuteReader(sql);

                while (reader.Read())
                {
                    CapInfo model = new CapInfo();

                    if (!(reader["id"] is DBNull))
                        model.id = reader["id"].ToInt32();
                    if (!(reader["pt"] is DBNull))
                        model.pt = reader["pt"].ToInt32();
                    if (!(reader["url"] is DBNull))
                        model.url = reader["url"].ToString();
                    if (!(reader["sort"] is DBNull))
                        model.sort = reader["sort"].ToInt32();
                    if (!(reader["url_crc"] is DBNull))
                        model.url_crc = Convert.ToInt64(reader["url_crc"]);
                    //if (!(reader["groupid"] is DBNull))
                    //    model.groupid = reader["groupid"].ToInt32();
                    //if (!(reader["computeno"] is DBNull))
                    //    model.computeno = reader["computeno"].ToInt32();
                    if (!(reader["update_time"] is DBNull))
                        model.update_time = reader["update_time"].ToDateTime();
                    if (!(reader["status"] is DBNull))
                        model.status = reader["status"].ToInt32();

                    list.Add(model);
                }
               
            }
            catch (MySqlException ex1)
            {
                AppConfigInfo.AppConfig.WriteMsg("MYSQL出现异常!异常原因:" + ex1.Message);
            
            }
            catch (Exception ex)
            {
                AppConfigInfo.AppConfig.WriteMsg("操作数据库出现异常!异常原因:" + ex.Message);
            }
            finally
            {
                if (reader!=null && !reader.IsClosed)
                {
                    reader.Close();
                }
                da.Dispose();
            }
            return list;

        }

    }
}
